using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class Asset : IAsset
    {
        // Methods
        public void Delete(int asset_id)
        {
            string cmdText = "UPDATE asset_info SET isdel='1' WHERE Asset_ID = " + asset_id;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public AssetInfo getAssetInfo(int Asset_id)
        {
            string cmdText = "SELECT asset_id,con_id,asset_type,asset_name,asset_desc,asset_path,smallasset_path,Asset_Width,Asset_Height,create_date,is_view,view_in_navigation,car_type,pic_type FROM asset_info where asset_id=" + Asset_id + " and isdel='0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            AssetInfo info = null;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = new AssetInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8), reader.GetDateTime(9).ToShortDateString(), reader.GetInt32(12), reader.GetInt32(13));
                    if (!reader.IsDBNull(10))
                    {
                        info.IsView = reader.GetInt32(10);
                    }
                    if (!reader.IsDBNull(11))
                    {
                        info.View_In_Navigation = reader.GetInt32(11);
                    }
                }
            }
            return info;
        }

        public IList getAssetList(int con_id)
        {
            return this.getAssetList(con_id, 0);
        }

        public IList getAssetList(int con_id, int category)
        {
            string cmdText = string.Concat(new object[] { "SELECT asset_id,con_id,asset_type,asset_name,asset_desc,asset_path,smallasset_path,Asset_Width,Asset_Height,create_date,Is_View,View_In_Navigation,car_type,pic_type FROM asset_Info where con_id =", con_id, " and asset_category=", category, " and isdel='0' " });
            if (con_id > 0)
            {
                object obj2 = cmdText;
                cmdText = string.Concat(new object[] { obj2, " and con_id=", con_id, " " });
            }
            cmdText = cmdText + " order by asset_id desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    AssetInfo info = new AssetInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8), reader.GetDateTime(9).ToShortDateString(), reader.GetInt32(10), reader.GetInt32(11));
                    if (!reader.IsDBNull(10))
                    {
                        info.IsView = reader.GetInt32(10);
                    }
                    if (!reader.IsDBNull(11))
                    {
                        info.View_In_Navigation = reader.GetInt32(11);
                    }
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getAssetList(int menuId, int con_id, int category)
        {
            string cmdText = string.Concat(new object[] { "SELECT asset_id,con_id,asset_type,asset_name,asset_desc,asset_path,smallasset_path,Asset_Width,Asset_Height,create_date,Is_View,View_In_Navigation,car_type,pic_type FROM asset_Info where asset_id in(select asset_id from asset_menu where menu_uid in (select menu_uid from dbo.GetChildCategories(", menuId, "))) and asset_category=", category, " and isdel='0' " });
            if (con_id > 0)
            {
                object obj2 = cmdText;
                cmdText = string.Concat(new object[] { obj2, " and con_id=", con_id, " " });
            }
            cmdText = cmdText + " order by asset_id desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    AssetInfo info = new AssetInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8), reader.GetDateTime(9).ToShortDateString(), reader.GetInt32(10), reader.GetInt32(11));
                    if (!reader.IsDBNull(10))
                    {
                        info.IsView = reader.GetInt32(10);
                    }
                    if (!reader.IsDBNull(11))
                    {
                        info.View_In_Navigation = reader.GetInt32(11);
                    }
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getAssetListIsView(int con_id)
        {
            string cmdText = "SELECT asset_id,con_id,asset_type,asset_name,asset_desc,asset_path,smallasset_path,Asset_Width,Asset_Height,create_date,Is_View,view_in_navigation,car_type,pic_type FROM asset_Info where con_id=" + con_id + " and isdel='0' and Is_View ='1' order by asset_id desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    AssetInfo info = new AssetInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8), reader.GetDateTime(9).ToShortDateString(), reader.GetInt32(10), reader.GetInt32(11));
                    if (!reader.IsDBNull(10))
                    {
                        info.IsView = reader.GetInt32(10);
                    }
                    if (!reader.IsDBNull(11))
                    {
                        info.View_In_Navigation = reader.GetInt32(11);
                    }
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getAssetListViewInNavigation(int con_id)
        {
            string cmdText = "SELECT asset_id,con_id,asset_type,asset_name,asset_desc,asset_path,smallasset_path,Asset_Width,Asset_Height,create_date,Is_View,view_in_navigation,car_type,pic_type FROM asset_Info where con_id=" + con_id + " and isdel='0' and view_in_navigation ='1' order by asset_id desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    AssetInfo info = new AssetInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8), reader.GetDateTime(9).ToShortDateString(), reader.GetInt32(10), reader.GetInt32(11));
                    if (!reader.IsDBNull(10))
                    {
                        info.IsView = reader.GetInt32(10);
                    }
                    if (!reader.IsDBNull(11))
                    {
                        info.View_In_Navigation = reader.GetInt32(11);
                    }
                    list.Add(info);
                }
            }
            return list;
        }

        public int getMaxAssetID()
        {
            SqlParameter[] cmdParms = new SqlParameter[0];
            string cmdText = "select max(asset_id) from asset_info";
            return (int)SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms);
        }

        public int getMenuIdByAssetId(int assetId)
        {
            string cmdText = "select menu_uid  from asset_menu where asset_id=" + assetId;
            SqlParameter[] parameterArray = new SqlParameter[0];
            return (int)SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null);
        }

        public void Insert(AssetInfo assetinfo)
        {
            string cmdText = string.Concat(new object[] { 
            "INSERT INTO asset_info(asset_name,con_id,asset_type,asset_desc,asset_path,Asset_Width,Asset_Height,smallasset_path,create_date,isdel,is_view,view_in_navigation, asset_category,car_type,pic_type,Is_EN) VALUES('", StringUtil.ReplaceStr(assetinfo.Asset_Name), "', ", assetinfo.Con_ID, ",'", assetinfo.Asset_Type, "','", StringUtil.ReplaceStr(assetinfo.Asset_Desc), "','", assetinfo.Asset_Path, "','", assetinfo.Asset_Width, "','", assetinfo.Asset_Height, "','", assetinfo.SmallAsset_Path, 
            "',getdate(),'0',", assetinfo.IsView, ",", assetinfo.View_In_Navigation, ", ", assetinfo.Asset_Category, ",",assetinfo.Car_Type,",",assetinfo.Pic_Type,",",assetinfo.Is_EN,")"
         });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void InsertAssetMenu(int assetId, int menuId)
        {
            int num = this.getMaxAssetID();
            string cmdText = string.Concat(new object[] { "insert into asset_menu(asset_id,menu_uid) values(", num, ",", menuId, ")" });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void Update(AssetInfo assetinfo)
        {
            string cmdText = string.Concat(new object[] { 
            "UPDATE asset_info SET asset_name = '", StringUtil.ReplaceStr(assetinfo.Asset_Name), "',car_type=",assetinfo.Car_Type,",pic_type=",assetinfo.Pic_Type,",asset_type = ", assetinfo.Asset_Type, ",asset_desc = '", StringUtil.ReplaceStr(assetinfo.Asset_Desc), "',asset_path = '", assetinfo.Asset_Path, "',smallasset_path ='", assetinfo.SmallAsset_Path, "',Asset_Width ='", assetinfo.Asset_Width, "',is_view =", assetinfo.IsView, ",View_In_Navigation =", assetinfo.View_In_Navigation, 
            ",Asset_Height ='", assetinfo.Asset_Height, "'  WHERE Asset_ID =", assetinfo.Asset_ID
         });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void UpdateMenuAsset(int assetId, int menuId)
        {
            string cmdText = string.Concat(new object[] { "UPDATE asset_menu SET menu_uid=", menuId, " WHERE Asset_ID =", assetId });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}
